import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import javax.annotation.Resource;
import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.zero.model.Student;


/**
 * JdbcTemplate执行CRUD操作
 *
 * @author Zero
 */
public class Demo2 {
    private JdbcTemplate jdbcTemplate;

    /**
     * 保存
     */
    @Test
    public void save() {
        int insert = jdbcTemplate.update("insert into student(sid,name,birthday,score) values(?,?,?,?)",
                "1906045179", "LiLi", "2021-8-9", 88.88);
        System.out.println("保存：" + insert);
    }

    /**
     * 修改
     */
    @Test
    public void update() {
        int update = jdbcTemplate.update("update student set sid=?,name=?,birthday=?,score=? where id=?",
                "ccc-update", "BBB", "2021-8-9", 11.22, 8);
        System.out.println("修改：" + update);
    }

    /**
     * 删除
     */
    @Test
    public void remove() {
        int delete = jdbcTemplate.update("delete from student where id=?", 8);
        System.out.println("删除：" + delete);
    }

    /**
     * 查询一个
     */
    @Test
    public void get() {
        Student student = jdbcTemplate.queryForObject("select * from student where id=?",
                new BeanPropertyRowMapper<>(Student.class),
                1);
        System.out.println("查询一个：" + student);
    }

    /**
     * 查询所有
     */
    @Test
    public void list() {
        //自己实现 RowMapper<T> 接口
//        List<Student> students = jdbcTemplate.query("select * from student", new StudentRowMapper());
        List<Student> students = jdbcTemplate.query("select * from student", new BeanPropertyRowMapper<>(Student.class));
        System.out.println("查询所有：");
        students.forEach(System.out::println);
    }

    /**
     * 查询一行一列
     */
    @Test
    public void getOne() {
        Long count = jdbcTemplate.queryForObject("select count(*) from student", Long.class);
        System.out.println("查询一行一列：" + count);
    }


    @Before
    public void init() {
        //获取 ApplicationContext 容器
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        //获取 JdbcTemplate 对象
        jdbcTemplate = applicationContext.getBean(JdbcTemplate.class);
    }
}

class StudentRowMapper implements RowMapper<Student> {
    @Override
    public Student mapRow(ResultSet resultSet, int i) throws SQLException {
        Student student = new Student();
        student.setId(resultSet.getInt("id"));
        student.setSid(resultSet.getString("sid"));
        student.setName(resultSet.getString("name"));
        if (Objects.nonNull(resultSet.getObject("birthday"))) {
            //getObject(String, Class<T>)方法如果获取数据库列字段为null，会抛出NPE
            student.setBirthday(resultSet.getObject("birthday", Date.class));
        }
        student.setScore(resultSet.getDouble("score"));
        return student;
    }
}
